/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package scrumprojekt;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

public class dbklass
{
  private Connection con = null;
  

  public dbklass()
      throws InformatikException
  {
    
    try {
      Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
      throw new InformatikException(e.getMessage());
    }
  }

  private void initConnection() throws InformatikException
  {
    if (this.con != null)
      closeConnection();
    try
    {
      this.con = DriverManager.getConnection("jdbc:mysql://83.253.70.18/scrumdb", "scrumdb", "");
      
    }
 
    catch (SQLException e)
    {
      throw new InformatikException(e);
    }
  }

  private void closeConnection() throws InformatikException
  {
    if (this.con != null) {
      try {
        this.con.close();
      } catch (SQLException e) {
        throw new InformatikException(e);
      }
      this.con = null;
    }
  }

  private boolean validateQuery(String query)
  {
    query = query.toLowerCase();

    if ((query.contains("delete from")) && (!query.startsWith("delete from"))) {
      return false;
    }

    if ((query.contains("create table")) || (query.contains("alter table")) || (query.contains("drop table"))) {
      return false;
    }
    return (!query.contains("create database")) && (!query.contains("drop database"));
  }

  public String fetchSingle(String query)
    throws InformatikException
  {
    String result = null;

    if (!validateQuery(query)) {
      throw new InformatikException("Query contains illegal statement.");
    }

    if (this.con == null) {
      initConnection();
    }

    try
    {
      Statement stmt = this.con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      if (rs.next())
        result = rs.getString(1);
    }
    catch (SQLException e) {
      throw new InformatikException(e);
    }

    closeConnection();

    return result;
  }

  public ArrayList<String> fetchColumn(String query)
    throws InformatikException
  {
    ArrayList result = new ArrayList();

    if (!validateQuery(query)) {
      throw new InformatikException("Query contains illegal statement.");
    }

    if (this.con == null) {
      initConnection();
    }

    try
    {
      Statement stmt = this.con.createStatement();
      ResultSet rs = stmt.executeQuery(query);

      while (rs.next())
        result.add(rs.getString(1));
    }
    catch (SQLException e) {
      throw new InformatikException(e);
    }

    closeConnection();

    return result;
  }

  public HashMap<String, String> fetchRow(String query)
    throws InformatikException
  {
    HashMap result = null;

    if (!validateQuery(query)) {
      throw new InformatikException("Query contains illegal statement.");
    }

    if (this.con == null) {
      initConnection();
    }

    try
    {
      Statement stmt = this.con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      ResultSetMetaData rsmd = rs.getMetaData();
      if (rs.next()) {
        result = new HashMap();
        for (int i = 1; i <= rsmd.getColumnCount(); i++)
          result.put(rsmd.getColumnLabel(i).toLowerCase(), rs.getString(i));
      }
    } catch (SQLException e) {
      throw new InformatikException(e);
    }

    closeConnection();

    return result;
  }

  public ArrayList<HashMap<String, String>> fetchRows(String query)
    throws InformatikException
  {
    ArrayList result = new ArrayList();

    if (!validateQuery(query)) {
      throw new InformatikException("Query contains illegal statement.");
    }

    if (this.con == null) {
      initConnection();
    }

    try
    {
      Statement stmt = this.con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      ResultSetMetaData rsmd = rs.getMetaData();
      while (rs.next()) {
        HashMap row = new HashMap();

        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
          row.put(rsmd.getColumnLabel(i).toLowerCase(), rs.getString(i));
        }
        result.add(row);
      }
    }
    catch (SQLException e) {
      throw new InformatikException(e);
    }

    closeConnection();

    return result;
  }

  public ArrayList<ArrayList<String>> fetchMatrix(String query)
    throws InformatikException
  {
    ArrayList result = new ArrayList();

    if (!validateQuery(query)) {
      throw new InformatikException("Query contains illegal statement.");
    }

    if (this.con == null) {
      initConnection();
    }

    try
    {
      Statement stmt = this.con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      ResultSetMetaData rsmd = rs.getMetaData();
      while (rs.next()) {
        result.add(new ArrayList());

        for (int i = 1; i <= rsmd.getColumnCount(); i++)
          ((ArrayList)result.get(result.size() - 1)).add(rs.getString(i));
      }
    }
    catch (SQLException e)
    {
      throw new InformatikException(e);
    }

    closeConnection();

    return result;
  }

  public String getAutoIncrement(String table, String attribut)
    throws InformatikException
  {
    if (fetchSingle("SELECT COUNT(*) FROM " + table).compareTo("0") != 0) {
      return fetchSingle("SELECT MAX(" + attribut + ") + 1 FROM " + table);
    }
    return "1";
  }

  public void insert(String query)
    throws InformatikException
  {
    if (!query.toLowerCase().startsWith("insert")) {
      throw new InformatikException("Illegal INSERT-query.");
    }
    executeUpdate(query);
  }

  public void delete(String query)
    throws InformatikException
  {
    if (!query.toLowerCase().startsWith("delete from")) {
      throw new InformatikException("Illegal DELETE-query.");
    }
    if (!query.toLowerCase().contains("where")) {
      throw new InformatikException("DELETE-query must contain a WHERE-clause.");
    }
    executeUpdate(query);
  }

  public void update(String query)
    throws InformatikException
  {
    if (!query.toLowerCase().startsWith("update")) {
      throw new InformatikException("Illegal UPDATE-query.");
    }
    executeUpdate(query);
  }

  private void executeUpdate(String query)
    throws InformatikException
  {
    if (!validateQuery(query)) {
      throw new InformatikException("Query contains illegal statement.");
    }

    if (this.con == null) {
      initConnection();
    }

    try
    {
      Statement stmt = this.con.createStatement();
      stmt.executeUpdate(query);
    }
    catch (SQLException e)
    {
      throw new InformatikException(e);
    }

    closeConnection();
  }
}